
package org.jetnuke.dao.generic;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.jforum.JForumExecutionContext;
import net.jforum.dao.generic.AutoKeys;
import net.jforum.util.preferences.SystemGlobals;

import org.jetnuke.entities.Stories;

public class GenericStoriesDAO extends AutoKeys implements org.jetnuke.dao.StoriesDAO {
	public Stories retrieveByPK(int storyId) throws Exception 
	{
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.selectByID"));
		p.setInt(1, storyId);
		
		ResultSet rs = p.executeQuery();
		Stories s = new Stories();
		
		if (rs.next()) {
			fillStoryFromResultSet(s, rs);
		}
		
		rs.close();
		p.close();

		return s;
	}

	/**
	 * @param s
	 * @param rs
	 */
	protected void fillStoryFromResultSet(Stories s, ResultSet rs) throws Exception{
		s.setACOMM(rs.getInt("ACOMM"));
		s.setAID(rs.getInt("AID"));
		s.setALANGUAGE(rs.getString("ALANGUAGE"));
		s.setASSOCIATED(rs.getString("ASSOCIATED"));
		s.setBODYTEXT(rs.getString("BODYTEXT"));
		s.setCATID(rs.getInt("CATID"));
		s.setCOMMENTS(rs.getInt("COMMENTS"));
		s.setCOUNTER(rs.getInt("COUNTER"));
		s.setHASPOLL(rs.getInt("HASPOLL"));
		s.setHOMETEXT(rs.getString("HOMETEXT"));
		s.setIHOME(rs.getInt("IHOME"));
		s.setINFORMANT(rs.getInt("INFORMANT"));
		s.setNOTES(rs.getString("NOTES"));
		s.setPOLLID(rs.getInt("POLLID"));
		s.setRATINGS(rs.getInt("RATINGS"));
		s.setSCORE(rs.getInt("SCORE"));
		s.setSID(rs.getInt("SID"));
		s.setTIME(rs.getTimestamp("TIME1"));
		s.setTITLE(rs.getString("TITLE"));
		s.setTOPIC(rs.getInt("TOPIC"));
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#incCounter(int)
	 */
	public void incCounter(int storyId) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.incCounter"));
		p.setInt(1, storyId);
		
		p.executeUpdate();
		p.close();
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#update(org.jetnuke.entities.Stories)
	 */
	public void update(Stories st) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.update"));
		
		p.setInt(1,st.getACOMM());
		p.setInt(2,st.getAID());
		p.setString(3,st.getALANGUAGE());
		p.setString(4,st.getASSOCIATED());
		p.setString(5,st.getBODYTEXT());
		p.setInt(6,st.getCATID());
		p.setInt(7,st.getCOMMENTS());
		p.setInt(8,st.getCOUNTER());
		p.setInt(9,st.getHASPOLL());
		p.setString(10,st.getHOMETEXT());
		p.setInt(11,st.getIHOME());
		p.setInt(12,st.getINFORMANT());
		p.setString(13,st.getNOTES());
		p.setInt(14,st.getPOLLID());
		p.setInt(15,st.getRATINGS());
		p.setInt(16,st.getSCORE());
		p.setTimestamp(17,new Timestamp(st.getTIME().getTime()));
		p.setString(18,st.getTITLE());
		p.setInt(19,st.getTOPIC());
		p.setInt(20,st.getSID());
		p.executeUpdate();
		p.close();
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#incComments(int)
	 */
	public void incComments(int id) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.incComments"));
		p.setInt(1, id);
		
		p.executeUpdate();
		p.close();
		
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getStoriesSidDesc(int)
	 */
	public List getStoriesByTopid_SidDesc(int topic,int limit) throws Exception {
		PreparedStatement p;
		
		if(topic==0){
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesSidDesc"));
			p.setInt(1, limit);
		}
		else{
			p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesSidDesc1"));
			p.setInt(1, topic);
			p.setInt(2, limit);
			
		}
		
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}

	/**
	 * @param rs
	 * @return
	 */
	protected List processSelectAll(ResultSet rs)throws Exception {
		List list = new ArrayList();
		while (rs.next()) {
			Stories u = new Stories();
			fillStoryFromResultSet(u,rs);			
			list.add(u);
		}
		
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#addNew(org.jetnuke.entities.Stories)
	 */
	public int addNew(Stories story) throws Exception {
		PreparedStatement p = this.getStatementForAutoKeys("StoryModel.addNew");
		this.setAutoGeneratedKeysQuery(SystemGlobals.getSql("StoriesModel.lastGeneratedStoryID"));
		initNewStory(story, p);
		
		int id = this.executeAutoKeysQuery(p);
				
		return id;
	}

	/**
	 * @param story
	 * @param p
	 */
	private void initNewStory(Stories st, PreparedStatement p) throws Exception{
		p.setInt(1,st.getACOMM());
		p.setInt(2,st.getAID());
		p.setString(3,st.getALANGUAGE());
		p.setString(4,st.getASSOCIATED());
		p.setString(5,st.getBODYTEXT());
		p.setInt(6,st.getCATID());
		p.setInt(7,st.getCOMMENTS());
		p.setInt(8,st.getCOUNTER());
		p.setInt(9,st.getHASPOLL());
		p.setString(10,st.getHOMETEXT());
		p.setInt(11,st.getIHOME());
		p.setInt(12,st.getINFORMANT());
		p.setString(13,st.getNOTES());
		p.setInt(14,st.getPOLLID());
		p.setInt(15,st.getRATINGS());
		p.setInt(16,st.getSCORE());
		p.setTimestamp(17,new Timestamp(st.getTIME().getTime()));
		p.setString(18,st.getTITLE());
		p.setInt(19,st.getTOPIC());
		
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#countAll()
	 */
	public int countAll() throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.countAll"));
		
		ResultSet rs = p.executeQuery();
		rs.next();
		
		int r= rs.getInt(1);
		rs.close();
		p.close();
		return r;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getTopByTopic(int)
	 */
	public Stories getTopByTopic(int topic) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getTopByTopic"));
		p.setInt(1, topic);
		
		ResultSet rs = p.executeQuery();
		Stories s = new Stories();
		
		if (rs.next()) {
			fillStoryFromResultSet(s, rs);
		}
		
		rs.close();
		p.close();

		return s;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getStoriesGreaterTime(java.util.Date)
	 */
	public List getStoriesGreaterTime(Date time) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesGreaterTime"));
		p.setTimestamp(1,new Timestamp(time.getTime()));
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#selectOrderBySidDescLimit(int)
	 */
	public List selectOrderBySidDescLimit(int i) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.selectOrderBySidDescLimit"));
		p.setInt(1,i);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getStoriesTimeDesc()
	 */
	public List getStoriesTimeDesc() throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesTimeDesc"));
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getByTopic(int)
	 */
	public List getByTopic(int topicid) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getByTopic"));
		p.setInt(1,topicid);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getByInformant(java.lang.String)
	 */
	public List getByInformant(String uname) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getByInformant"));
		p.setString(1,uname);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc(java.lang.String, int, int, java.lang.String)
	 */
	public List searchOrderTimeDesc(String author, int category, int topicid, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc"));
		p.setString(1,author);
		p.setInt(2,category);
		p.setInt(3,topicid);
		p.setString(4,query);
		p.setString(5,query);
		p.setString(6,query);
		p.setString(7,query);	
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc2(java.lang.String, int, java.lang.String)
	 */
	public List searchOrderTimeDesc2(String author, int category, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc2"));
		p.setString(1,author);
		p.setInt(2,category);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);
		p.setString(6,query);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc3(java.lang.String, int, java.lang.String)
	 */
	public List searchOrderTimeDesc3(String author, int topicid, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc3"));
		p.setString(1,author);
		p.setInt(2,topicid);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);
		p.setString(6,query);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc4(java.lang.String, java.lang.String)
	 */
	public List searchOrderTimeDesc4(String author, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc4"));
		p.setString(1,author);
		p.setString(2,query);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc5(int, int, java.lang.String)
	 */
	public List searchOrderTimeDesc5(int category, int topicid, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc5"));
		p.setInt(1,category);
		p.setInt(2,topicid);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);
		p.setString(6,query);
		
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc6(int, java.lang.String)
	 */
	public List searchOrderTimeDesc6(int category, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc6"));
		p.setInt(1,category);
		p.setString(2,query);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);		
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc7(int, java.lang.String)
	 */
	public List searchOrderTimeDesc7(int topicid, String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc7"));
		p.setInt(1,topicid);
		p.setString(2,query);
		p.setString(3,query);
		p.setString(4,query);
		p.setString(5,query);

		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#searchOrderTimeDesc8(java.lang.String)
	 */
	public List searchOrderTimeDesc8(String query) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.searchOrderTimeDesc8"));
		p.setString(1,query);
		p.setString(2,query);
		p.setString(3,query);
		p.setString(4,query);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#selectOrderByCounterDescLimit(int)
	 */
	public List selectOrderByCounterDescLimit(int limit) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.selectOrderByCounterDescLimit"));
		p.setInt(1,limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#selectVotedOrderScoreDesc(int)
	 */
	public List selectVotedOrderScoreDesc(int limit) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.selectVotedOrderScoreDesc"));
		p.setInt(1,limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#selectCommentedOrderCommentDesc(int)
	 */
	public List selectCommentedOrderCommentDesc(int limit) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.selectCommentedOrderCommentDesc"));
		p.setInt(1,limit);
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getStoriesInTime(java.util.Date, java.util.Date)
	 */
	public List getStoriesInTime(Date time, Date time2) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesInTime"));
		p.setTimestamp(1,new Timestamp(time.getTime()));
		p.setTimestamp(2,new Timestamp(time2.getTime()));
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		return list;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#deleteByID(int)
	 */
	public void deleteByID(int id) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.deleteByID"));
		p.setInt(1,id);
		p.executeUpdate();
		p.close();
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#countByCatID(int)
	 */
	public int countByCatID(int catid) throws Exception {
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.countByCatID"));
		p.setInt(1,catid);
		ResultSet rs = p.executeQuery();
		rs.next();
		
		int r= rs.getInt(1);
		rs.close();
		p.close();
		return r;
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#deleteByCatID(int)
	 */
	public void deleteByCatID(int catid) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.deleteByCatID"));
		p.setInt(1,catid);
		p.executeUpdate();
		p.close();
		
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#moveCat(int, int)
	 */
	public void moveCat(int old_cat, int new_cat) throws Exception {
		PreparedStatement p=JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.moveCat"));
		p.setInt(1,new_cat);
		p.setInt(2,old_cat);
		p.executeUpdate();
		p.close();
		
	}

	/* (non-Javadoc)
	 * @see org.jetnuke.model.StoriesModel#getStoriesByCategory_SidDesc(int, int)
	 */
	public List getStoriesByCategory_SidDesc(int catid, int limit) throws Exception {
		
		PreparedStatement p = JForumExecutionContext.getConnection().prepareStatement(SystemGlobals.getSql("StoryModel.getStoriesByCategory_SidDesc"));
		p.setInt(1, catid);
		p.setInt(2, limit);
		
		ResultSet rs = p.executeQuery();
		List list = this.processSelectAll(rs);
		rs.close();
		p.close();
		
		return list;
	}
}
